<?php
/**
 *This file contains the Mysqli Connection class
 *@author Muhammad El-Hindi <mu7eh7@googlemail.com>
 *6 Jun 2012
 *@copyright Copyright (c) 2012, Tuncay Bakan
 *@package lib
 *
 */
/**
 *Establish connection to a DB with mysqli driver
 *This class provides some other functions beyond the mysqli class
 */
class Mysqli_dbconnection extends  Dbconnection{
    //singleton Instance
    private static $oInstance;
    //DB resource
    private $oDBConnection;
    //table that is used for querys
    private $sDefaultTable;
    //default column to identify a row
    private $sDefaultColumn;
    //Query to perform
    private $sQuery;
    //oResult holds data retrieved from server
    private $oResult;

    /**
     * Creates an instance and establishs connection to db
     */
    private function __construct(){
        //connect using connection infos in config
        $this->connect(DBHOST,DBUSER,DBPASSWORD,DBNAME);
    }

    /**
     * Static method to get singelton instance
     */
    public static function getInstance(){
        //if singelton was not created before
        if (is_null(self::$oInstance)){
            //create singelton instance
            self::$oInstance = new self;
        }
        //set default table to ''
        self::$oInstance->sDefaultTable = '';
        //return the singelton instance
        return self::$oInstance;
    }

    /**
     * Creates a new mysqli Connection to a database
     *
     * @param String $sHost The database host
     * @param String $sUser The user to logon with in database host
     * @param String $sPassword The password of the user specified before
     * @param String $sDatabase The databse on the host to connect to
     */
    protected function connect($sHost, $sUser, $sPassword, $sDatabase)
    {
        //create new mysqli connection
        $bLink = $this->oDBConnection = new mysqli
        (
        $sHost , $sUser , $sPassword , $sDatabase
        );

        //Check if connection could be established
        if (!$bLink) {
            die('Cannot establish SQL connection: ' . mysql_error());
        }
    }

    /**
     * Disconnects from database
     */
    public function disconnect(){
        //call disconnect method of mysqli instance
        $this->oDBConnection->close();
    }

    /**
     * Sets the default table for querys
     *
     * @param String $sDefaultTable Use if string value shall be assigned
     * @param String $sSQLStatment Use when value is a sql statement, set first parameter to ""
     */
    public function _setDefaultTable($sDefaultTable){
        if ($sDefaultTable != ""){
            $this->sDefaultTable = $sDefaultTable;
        }
    }

    /**
     * Sets the default column with which rows are selected
     *
     * @param String $sDefaultColumn Use if string value shall be assigned
     * @param String $sSqlStatmentUse if sql statment shall be assigned, set first parameter to ""
     */
    public function _setDefaultColumn($sDefaultColumn){
        if ($sDefaultColumn != ""){
            $this->sDefaultColumn = $sDefaultColumn;
        }

    }

    /**
     * Stores query in instance variable sQuery
     *
     * @param String $sQuery The query to prepare
     */
    public function prepare($sQuery){
        $this->sQuery = $sQuery;
    }

    /**
     * Performs a query on the database
     *
     * @param String $sQuery [Optional] Query to be performed on database
     */
    public function query($sQuery=""){
        //store the query in instance variable "sQuery" if it is not empty
        if ($sQuery != ""){
            $this->prepare($sQuery);
        }

        //check if instance variable "sQuery" is not empty
        if (isset($this->sQuery))
        {
            //execute prepared query and store the result in result variable
            $this->oResult = $this->oDBConnection->query($this->sQuery);
            $bSuccessful = !(empty($this->oResult));
            return $bSuccessful;
        }

        return FALSE;
    }

    /**
     * Runs a sql query to get a row from the default table identified by the default column
     * the result is stored in the instance variable sResult
     *
     * @param mixed $mValue The value that should be matched in the default column
     * @param String $sValueType The type that the values in the default columns have
     * @param String $sDefinedValue Used to assign a sql statment
     */
    public function getRow($mValue,$sValueType, $sDefinedValue=""){
        $mValue = getSQLValueString($mValue, $sValueType, $sDefinedValue);
        if(isset($this->sDefaultTable)&&isset($this->sDefaultColumn)){
            $this->query("SELECT * FROM $this->sDefaultTable WHERE $this->sDefaultColumn = $mValue");
            return TRUE;
        }

        return FALSE;

    }

    /**
     * Runs a SELECT on the database and stores result in the instance variable sResult
     *
     * @param String $sAttributes The attributes to select from the table
     * @param String $sTable The table to perform the SELECT on
     * @param String $sColumn [optional] The column for WHERE statment if empty result is entire table
     * @param String $sValue [optional] The value for WHERE statment, needed when $sColumn is not ""
     */
    public function performSelect($sAttributes, $sTable="", $sCondition="", $sColumn="", $sValue=""){
        //if sTable is empty use default Table
        if ($sTable == ""){
            $sTable = $this->sDefaultTable;
        }

        if ($sValue != ""){
            //if sColumn matches default use default column
            if ($sColumn == ""){
                $sColumn = $this->sDefaultColumn;
            }
            $this->query("SELECT $sAttributes FROM $sTable WHERE $sColumn = $sValue");
        }else{
            if($sCondition == ""){
                return $this->query("SELECT $sAttributes FROM $sTable");
            } else {
                return $this->query("SELECT $sAttributes FROM $sTable WHERE $sCondition");
            }
        }

    }

    /**
     * Runs a INSERT on the database and stores result in the instance variable sResult
     * @param string $sTable [optional] Table in which the new record will be created
     * @param string  $sAttributes Attributs that will be set
     * @param string $sValues Values of the attributes specified in $sAttributes
     */
    public function performInsert($sAttributes, $sValues, $sTable="" ){
        //if sTable is empty use default Table
        if ($sTable == ""){
            $sTable = $this->sDefaultTable;
        }

        //attributes are specified
        if ($sAttributes != ""){
            return $this->query("INSERT INTO $sTable ($sAttributes) VALUES ($sValues);");

        }

        echo (mysql_error());

        return FALSE;

    }

    /**
     * Runs a UPDATE on the database
     *
     * @param array  $aAttributes Array with new values. Has the form of "column"=>"value"
     * @param string $sWhereValue [optional] Value that should be matched in the column of the WHERE clause
     * @param string $sTable [optional] Table on which UPDATE should be performed
     * @param string $sWhereColumn [optional] Column for WHERE clause
     */
    public function performUpdate($aAttributes, $sCondition="",$sWhereValue ="", $sTable="", $sWhereColumn=""){
        end($aAttributes);
        $sLastKey = key($aAttributes);

        $sAttributes ="";
        foreach ($aAttributes as $sKey => $sValue){
            $sAttributes .= $sKey .'=\'' . $sValue . '\'';
            if ($sKey != $sLastKey){
                $sAttributes .= ',';
            }
        }
        //if sTable is empty use default Table
        if ($sTable == ""){
            $sTable = $this->sDefaultTable;
        }

        if ($sWhereValue != ""){
            //if sWhereColumn matches default use default column
            if ($sWhereColumn == ""){
                $sWhereColumn = $this->sDefaultColumn;
            }



            return $this->query("Update $sTable SET $sAttributes WHERE $sWhereColumn = $sWhereValue");
        }else{
            return $this->query("Update $sTable SET $sAttributes WHERE $sCondition");
        }
        return FALSE;
    }

    /**
     * Delete a row from a database table
     *
     * @param string $sWhereValue specifies which row to delete
     * @param string $sTable [optional] Table to delete row from
     * @param string $sWhereColumn [optional] Column for deletion condition
     */
    public function performDelete($sWhereValue, $sTable="", $sWhereColumn=""){
        //if sTable is empty use default Table
        if ($sTable == ""){
            $sTable = $this->sDefaultTable;
        }

        if ($sWhereValue != ""){
            //if sColumn is empty use default column
            if ($sWhereColumn == ""){
                $sWhereColumn = $this->sDefaultColumn;
            }

            return $this->query("DELETE FROM $sTable WHERE $sWhereColumn = $sWhereValue;");
        }

        return FALSE;
    }


    /**
     * Returns the result of a sql query in the specified type
     * default type is object
     * @param String $sType Type in which the result shall be returned
     */
    public function fetch($sType = 'array'){
        //check if instance variable sResult is set
        if (isset($this->oResult))
        {
            switch ($sType)
            {
                //return result as array
                case 'array':

                    //fetch a result as array of arrays
                    //$aRow = $this->oResult->fetch_array(MYSQLI_NUM);
                    $aRow = $this->oResult->fetch_all(MYSQLI_ASSOC);
                    //print_r($aRow);

                    break;

                    //return result as object
                case 'object':

                    $aRow = $this->oResult->fetch_object();

                    break;
                default:

                    //fetch a row as array of arrays
                    $aRow = $this->oResult->fetch_all(MYSQLI_ASSOC);

                    break;
            }

            return $aRow;
        }

        return FALSE;
    }

    /**
     * Disconnects from database when object is destructed
     */
    public function __destruct(){
        $this->disconnect();
    }

    /**
     * Prevents sqlinjections with variables
     *
     * @param mixed $mValue
     * @param String $sType
     * @param mixed $mDefinedValue Can be used to return a self defined value, e.g. when an SQL statment must be used
     * @param mixed $mNotDefinedValue Will be returned if a defined value does not exists
     * @return returns the value after removing sqlinjection possibilities
     *
     * @author http://board.gulli.com/thread/1229328-reicht-mysql-real-escape-string-aus/
     */
    public function getSQLValueString($mValue, $sValueType, $sDefinedValue = "", $sNotDefinedValue = ""){
        $mValue = get_magic_quotes_gpc() ? stripslashes($mValue) : $mValue;

        $mValue = function_exists("mysqli_real_escape_string") ? $this->oDBConnection->real_escape_string($mValue) : $this->oDBConnection->escape_string($mValue);

        switch ($sValueType) {
            case "string":
                $mValue = ($mValue != "") ? "'" . $mValue . "'" : "NULL";
                break;
            case "long":
            case "int":
                $mValue = ($mValue != "") ? intval($mValue) : "NULL";
                break;
            case "double":
                $mValue = ($mValue != "") ? "'" . doubleval($mValue) . "'" : "NULL";
                break;
            case "date":
                $mValue = ($mValue != "") ? "'" . $mValue . "'" : "NULL";
                break;
            case "defined":
                $mValue = ($mValue != "") ? $sDefinedValue : $sNotDefinedValue;
                break;
        }
        return $mValue;
    }

}
?>